﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace Mind.Data
{

    public static class SqlSyntaxConverter
    {
        public static string GetParameterPrefix(DataAccessProvider provider)
        {
            switch (provider)
            {
                case DataAccessProvider.MySQL:
                    return "?";
                default:
                    return "@";
                    
            }
        }

        public static string FormatParameterName(string parameterName, DataAccessProvider provider)
        {
            if (parameterName.StartsWith("@", StringComparison.InvariantCultureIgnoreCase))
            {
                if (provider != DataAccessProvider.MySQL)
                    return parameterName;
                parameterName = parameterName.Remove(0, 1);
            }
            return String.Format("{0}{1}", GetParameterPrefix(provider), parameterName);
        }


        

        public static string FormatName(string name, DataAccessProvider provider)
        {
            if (provider == DataAccessProvider.MySQL)
                return string.Format("`{0}`", name);
            else
                return string.Format("[{0}]", name);
        }

        public static void BuildTeste()
        {
            System.Diagnostics.Debug.Write("BuildTeste");
        }


        private static string SimpleRegexReplace(string text, string oldValue, string newValue)
        {
            string result = text;
            Regex rx = new Regex(oldValue, RegexOptions.IgnoreCase);
            Match m = rx.Match(text);
            if (m.Success)
            {
                result = result.Replace(m.Value, newValue);
            }

            return result;
        }

        public static string CheckSyntax(DataAccessProvider provider, string sql)
        {
            if (provider == DataAccessProvider.MySQL || provider == DataAccessProvider.SQLite)
            {
                //TOP to LIMIT convertion:
                Regex rx = new Regex(@"TOP (?<limit>\d+)", RegexOptions.IgnoreCase);
                Match m = rx.Match(sql);
                if (m.Success)
                {
                    sql = sql.Replace(m.Value, String.Empty);
                    sql = String.Format("{0} LIMIT {1}", sql, m.Groups["limit"].Value);
                }

                //TODO: For SQLite maybe OK but it's a workaround for MySQL.
                sql = SimpleRegexReplace(sql, @"WITH\ *\(XLOCK\)", String.Empty);

                //Getting the last auto inc ID.
                if (provider == DataAccessProvider.SQLite)
                    sql = SimpleRegexReplace(sql, @"SELECT\ *@@IDENTITY", "; SELECT last_insert_rowid()");
                else if (provider == DataAccessProvider.MySQL)
                {
                    sql = SimpleRegexReplace(sql, @"SELECT\ *@@IDENTITY", "; SELECT LAST_INSERT_ID()");
                    sql = sql.Replace("@", "?");
                }
            }


            return sql;
        }
    }
}


